package cn.hd01.service.impl;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.domain.Specifications;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import cn.hd01.repository.OrderProductRepository;
import cn.hd01.repository.entity.OrderProduct;
import cn.hd01.service.OrderProductService;

@Service
public class OrderProductServiceImpl extends BaseServiceImpl<OrderProduct, Integer> implements OrderProductService {

	private static String[] SORTS = new String[]{"", "product_sale_price asc", "product_sale_price desc", "operation_count asc", "operation_count desc"};
	private OrderProductRepository repository;
	
	@Autowired
	private EntityManager em;
	
	@Autowired
	public OrderProductServiceImpl(OrderProductRepository repository) {
		super(repository);
		this.repository = repository;
	}

	@Override
	public Page<OrderProduct> findAll(final OrderProduct orderProduct, Pageable pageable) {
		if (orderProduct == null) {
			return repository.findAll(pageable);
		}

		return repository.findAll(Specifications.where(new Specification<OrderProduct>() {
			@Override
			public Predicate toPredicate(Root<OrderProduct> r, CriteriaQuery<?> q, CriteriaBuilder cb) {
				List<Predicate> predicates = new ArrayList<Predicate>();

				return cb.and(predicates.toArray(new Predicate[] {}));
			}
		}), pageable);
		
	}

	@Override
	@SuppressWarnings("unchecked")
	public Page<OrderProduct> pageOrderList(int sort, OrderProduct u, Pageable pageable) {
		String sql = "select cp.id,cp.order_id,cp.product_name,cp.product_id,sum(product_sale_price) as product_sale_price, sum(discount_sale_price) as  discount_sale_price, sum(operation_count) as operation_count, cp.create_time,cp.update_time from  c_order_product cp left join product p on cp.product_id = p.id where p.parent_id is not null ";
		
		if(u.getParentId() != null){
			sql += " and p.parent_id = " + u.getParentId() + " ";
		}
		
		if (StringUtils.hasText(u.getBeginDate())) {
			sql += " and DATE_FORMAT(cp.create_time, '%Y-%m-%d')>=DATE_FORMAT('"+u.getBeginDate()+"', '%Y-%m-%d') ";
		}
		if (StringUtils.hasText(u.getEndDate())) {
			sql += " and DATE_FORMAT(cp.create_time, '%Y-%m-%d')<=DATE_FORMAT('"+u.getEndDate()+"', '%Y-%m-%d') ";
		}

		sql += " group by product_id";
		
		if(StringUtils.hasText(SORTS[sort])){
			sql += " order by " + SORTS[sort];
		}
		
		Query query = em.createNativeQuery(sql, OrderProduct.class);
		query.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
		query.setMaxResults(pageable.getPageSize());
		List<OrderProduct> objectList = query.getResultList();

		return new PageImpl<OrderProduct>(objectList, pageable, total(u));
	}

	private long total(OrderProduct u) {
		String sql = "select count(1) from (select cp.id,cp.order_id,cp.product_name,cp.product_id,sum(product_sale_price) as product_sale_price, sum(discount_sale_price) as  discount_sale_price, sum(operation_count) as operation_count, cp.create_time,cp.update_time from  c_order_product cp left join product p on cp.product_id = p.id where p.parent_id is not null ";
		
		if(u.getParentId() != null){
			sql += " and p.parent_id = " + u.getParentId() + " ";
		}
		
		if (StringUtils.hasText(u.getBeginDate())) {
			sql += " and DATE_FORMAT(cp.create_time, '%Y-%m-%d')>=DATE_FORMAT('"+u.getBeginDate()+"', '%Y-%m-%d') ";
		}
		if (StringUtils.hasText(u.getEndDate())) {
			sql += " and DATE_FORMAT(cp.create_time, '%Y-%m-%d')<=DATE_FORMAT('"+u.getEndDate()+"', '%Y-%m-%d') ";
		}

		sql += " group by product_id) t";
		Query query = em.createNativeQuery(sql);
		return new Long(query.getSingleResult().toString()).longValue();
	}
	
	@Override
	public Integer getProductCount(Integer id) {
		return repository.getProductCount(id);
	}

	@Override
	public List<OrderProduct> findByOrderId(Integer orderId) {
		return repository.findByOrderId(orderId);
	}

	@Override
	public int deleteByOrderId(Integer orderId) {
		return repository.deleteByOrderId(orderId);
	}

	@Override
	public List<OrderProduct> findParentOrderList(OrderProduct u) {
		String sql = "SELECT p.parent_id as id,cp.order_id,pp.name AS product_name,cp.product_id,sum(product_sale_price) as product_sale_price, sum(discount_sale_price) as  discount_sale_price, sum(operation_count) as operation_count, cp.create_time,cp.update_time FROM c_order_product cp  LEFT JOIN product p ON cp.product_id = p.id LEFT JOIN product pp ON p.parent_id = pp.id where 1 = 1";
		
		if(u.getParentId() != null){
			sql += " and p.parent_id = " + u.getParentId() + " ";
		}
		
		if (StringUtils.hasText(u.getBeginDate())) {
			sql += " and DATE_FORMAT(cp.create_time, '%Y-%m-%d')>=DATE_FORMAT('"+u.getBeginDate()+"', '%Y-%m-%d') ";
		}
		if (StringUtils.hasText(u.getEndDate())) {
			sql += " and DATE_FORMAT(cp.create_time, '%Y-%m-%d')<=DATE_FORMAT('"+u.getEndDate()+"', '%Y-%m-%d') ";
		}
		sql +=  " group BY p.parent_id";
		Query query = em.createNativeQuery(sql, OrderProduct.class);
		List<OrderProduct> objectList = query.getResultList();
		return objectList;
	}

	@Override
	public int updateProductName(String productName, Integer productId) {
		return repository.updateProductName(productName, productId);
	}
}
